package oop.db.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Set;

import oop.data.BaseQuestion;
import oop.data.Question;
import oop.data.Text;
import oop.data.Topic;
import oop.db.Database;
import oop.util.Utils;

public class QuestionDAO {

	public static List<BaseQuestion> fetchByTopic(long topicId, int start,
			int length) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			Topic topic = TopicDAO.fetchById(topicId);
			Set<Topic> descendants = topic.getDescendants();
			StringBuilder sb = new StringBuilder();
			for (Topic descendant : descendants) {
				sb.append(descendant.getId()).append(", ");
			}
			if (sb.length() > 0) {
				sb.delete(sb.length()-2, sb.length());
			}
			String descendantsStr = sb.toString(); 
			
			String sql = "SELECT ques_id, ques_content, ques_level, ques_topic, "
					+ "ques_author, ques_mark, ques_create_date "
					+ "FROM  tblQuestion "
					+ "WHERE ques_topic IN (" + descendantsStr + ") " 
					+ "LIMIT ?, ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, start);
			stmt.setInt(2, length);
			rs = stmt.executeQuery();
			return QuestionDAO.fromResultSetBase(rs);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static List<BaseQuestion> fetchByAuthor(long authorId, int start,
			int length) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ques_id, ques_content, ques_level, ques_topic, "
					+ "ques_author, ques_mark, ques_create_date "
					+ "FROM  tblQuestion, tblUser "
					+ "WHERE ques_author = user_id "
					+ "AND user_id = ? " + "LIMIT ?, ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, authorId);
			stmt.setInt(2, start);
			stmt.setInt(3, length);
			rs = stmt.executeQuery();
			return fromResultSetBase(rs);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}
	
	public static List<BaseQuestion> fetch(int start, int length)
	throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ques_id, ques_content, ques_level, ques_topic, "
				+ "ques_author, ques_mark, ques_create_date "
				+ "FROM  tblQuestion "
				+ "ORDER BY ques_create_date DESC "
				+ "LIMIT ?, ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, start);
			stmt.setInt(2, length);
			rs = stmt.executeQuery();
			return fromResultSetBase(rs);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static BaseQuestion fetchByIdBase(long questionId) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ques_id, ques_content, ques_level, ques_topic, "
					+ "ques_author, ques_mark, ques_create_date "
					+ "FROM  tblQuestion "
					+ "WHERE ques_id = " + questionId;

			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			return Utils.single(fromResultSetBase(rs));
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static Question fetchById(long sectionId, long questionId) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT tblQuestion.ques_id, ques_content, ques_level, "
					+ "ques_topic, ques_author, ques_mark, sect_id, ques_create_date "
					+ "FROM tblQuestion, tblSectionQuestion "
					+ "WHERE tblQuestion.ques_id = tblSectionQuestion.ques_id " 
						+ "AND sect_id = ? AND tblQuestion.ques_id = ?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, sectionId);
			stmt.setLong(2, questionId);
			rs = stmt.executeQuery();

			if (rs.next()) {
				return new Question(rs.getLong(1), rs.getLong(2), rs
						.getInt(3), rs.getLong(4), rs.getLong(5),
						rs.getLong(7), rs.getTimestamp(8));
			}
			return null;
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static List<BaseQuestion> fromResultSetBase(ResultSet rs)
			throws SQLException {
		List<BaseQuestion> questions = new ArrayList<BaseQuestion>();
		while (rs.next()) {
			questions.add(new BaseQuestion(rs.getInt(1), rs.getLong(2), rs
					.getInt(3), rs.getLong(4), rs.getLong(5), rs.getTimestamp(7)));
		}
		return questions;
	}

	public static long count() throws SQLException {
		String sql = "SELECT COUNT(*) FROM  tblQuestion";
		return Database.get().count(sql);
	}

	public static long countByTopic(int topicId) throws SQLException {
		Topic topic = TopicDAO.fetchById(topicId);
		Set<Topic> descendants = topic.getDescendants();
		StringBuilder sb = new StringBuilder();
		for (Topic descendant : descendants) {
			sb.append(descendant.getId()).append(", ");
		}
		if (sb.length() > 0) {
			sb.delete(sb.length() - 2, sb.length());
		}
		String descendantsStr = sb.toString();

		return Database.get().count(
				"SELECT COUNT(*) " + "FROM  tblQuestion "
						+ "WHERE ques_topic IN (" + descendantsStr + ")");
	}

	public static long countByAuthor(int authorId) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT COUNT(*) " + "FROM  tblQuestion "
					+ "WHERE ques_author = ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, authorId);
			rs = stmt.executeQuery();
			rs.next();
			return rs.getInt(1);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static BaseQuestion create(String content, int level, long topicId,
			long authorId, int mark) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "INSERT INTO tblQuestion (ques_content, ques_level, " 
					+ "ques_topic, ques_author, ques_mark, ques_create_date) "
					+ "VALUES (?, ?, ?, ?, ?, ?)";

			conn = Database.get().getConnection();
			Text text = TextDAO.create(content);
			stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			stmt.setLong(1, text.getId());
			stmt.setInt(2, level);
			stmt.setLong(3, topicId);
			stmt.setLong(4, authorId);
			stmt.setInt(5, mark);
			Date now = new Date();
			stmt.setTimestamp(6, new Timestamp(now.getTime()));
			stmt.executeUpdate();

			rs = stmt.getGeneratedKeys();
			rs.next();
			long id = rs.getLong(1);

			return new BaseQuestion(id, text.getId(), level, topicId, authorId, now);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static void persist(BaseQuestion question) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			String sql = "UPDATE tblQuestion SET "
					+ "ques_content=?, ques_level=?, ques_topic=?, ques_author=? "
					+ "WHERE ques_id = ?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, question.getContent().getId());
			stmt.setInt(2, question.getLevel());
			stmt.setLong(3, question.getTopic().getId());
			stmt.setLong(4, question.getAuthor().getId());
			stmt.setLong(5, question.getId());
			stmt.executeUpdate();
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static List<Question> fetchBySection(long sectionId) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT tblQuestion.ques_id, ques_content, ques_level, "
					+ "ques_topic, ques_author, ques_mark, sect_id, ques_create_date "
					+ "FROM tblQuestion, tblSectionQuestion "
					+ "WHERE tblQuestion.ques_id = tblSectionQuestion.ques_id "
					+ "AND sect_id = ? " 
					+ "ORDER BY timestamp ASC";
	
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, sectionId);
			rs = stmt.executeQuery();
			return fromResultSet(rs);
		} finally {
			if (rs != null)	rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	private static List<Question> fromResultSet(ResultSet rs)
			throws SQLException {
		List<Question> questions = new ArrayList<Question>();
		while (rs.next()) {
			questions.add(new Question(rs.getLong(1), rs.getLong(2), rs
					.getInt(3), rs.getLong(4), rs.getLong(5), rs.getLong(7), rs
					.getTimestamp(8)));
		}
		return questions;
	}

	public static int drop(long questionId) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		try {
			String sql = "DELETE FROM tblQuestion WHERE ques_id = " + questionId;
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			return stmt.executeUpdate(sql);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static List<BaseQuestion> fetchByContent(String content, int limit)
			throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ques_id, ques_content, ques_level, ques_topic, "
					+ "ques_author, ques_mark, ques_create_date "
					+ "FROM tblQuestion "
					+ "WHERE ques_content LIKE ? " 
					+ "ORDER BY ques_content ASC " 
					+ "LIMIT ?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, content);
			stmt.setLong(2, limit);
			rs = stmt.executeQuery();

			return fromResultSetBase(rs);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static int[][][] fetchCounts(long testId, long[] topicIds, int[] levels, int[] marks)
			throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			StringBuilder where = new StringBuilder();
			StringBuilder select = new StringBuilder("SELECT ");
			StringBuilder groupBy = new StringBuilder();
			StringBuilder orderBy = new StringBuilder();
			if (topicIds[0] >= 0) {
				select.append("ques_topic, ");
				where.append("ques_topic IN (").append(join(topicIds)).append(") AND ");
				groupBy.append("ques_topic, ");
				orderBy.append("ques_topic ASC, ");
			} else {
				select.append(topicIds[0]).append(", ");
			}
			if (levels[0] >= 0) {
				select.append("ques_level, ");
				where.append("ques_level IN (").append(join(levels)).append(") AND ");
				groupBy.append("ques_level, ");
				orderBy.append("ques_level ASC, ");
			} else {
				select.append(levels[0]).append(", ");
			}
			if (marks[0] >= 0) {
				select.append("ques_mark, ");
				where.append("ques_mark IN (").append(join(marks)).append(") AND ");
				groupBy.append("ques_mark, ");
				orderBy.append("ques_mark ASC, ");
			} else {
				select.append(marks[0]).append(", ");
			}
			if (where.length() > 0) {
				where.delete(where.length()-4, where.length()).insert(0, "WHERE ").append("AND ");
				groupBy.delete(groupBy.length()-2, groupBy.length()).insert(0, "GROUP BY ");
				orderBy.delete(orderBy.length()-2, orderBy.length()).insert(0, " ORDER BY ");
			}
			where.append("ques_id NOT IN ")
					.append("(SELECT ques_id FROM vwTestQuestion WHERE test_id = ")
					.append(testId).append(")");
			
			String sql = select + "COUNT(ques_id) " +
					"FROM tblQuestion " + where + groupBy + orderBy;
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			int[][][] c = new int[topicIds.length][levels.length][marks.length];
			while (rs.next()) {
				long topic = rs.getLong(1);
				int level = rs.getInt(2);
				int mark = rs.getInt(3);
				int count = rs.getInt(4);
				
				int i = Arrays.binarySearch(topicIds, topic);
				int j = Arrays.binarySearch(levels, level);
				int k = Arrays.binarySearch(marks, mark);
				c[i][j][k] = count;
			}
			return c;
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	private static String join(long[] arr) {
		StringBuilder sb = new StringBuilder();
		for (long value : arr) {
			sb.append(value).append(", ");
		}
		if (sb.length() > 0) {
			sb.delete(sb.length() - 2, sb.length());
		}
		return sb.toString();
	}

	private static String join(int[] arr) {
		StringBuilder sb = new StringBuilder();
		for (int value : arr) {
			sb.append(value).append(", ");
		}
		if (sb.length() > 0) {
			sb.delete(sb.length() - 2, sb.length());
		}
		return sb.toString();
	}

	public static List<BaseQuestion> fetchByInfo(long testId, int level,
			int mark, long topicId, int count) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			StringBuilder where = new StringBuilder("WHERE ");
			if (level >= 0) {
				where.append("ques_level = ").append(level).append(" AND ");
			}
			if (mark >= 0) {
				where.append("ques_mark = ").append(mark).append(" AND ");
			}
			if (topicId > 0) {
				where.append("ques_topic = ").append(topicId).append(" AND ");
			}
			where.append("ques_id NOT IN ("
					+ "SELECT ques_id FROM vwTestQuestion WHERE test_id = ?) ");

			String sql = "SELECT ques_id, ques_content, ques_level, ques_topic, "
					+ "ques_author, ques_mark, ques_create_date " 
					+ "FROM tblQuestion "
					+ where
					+ "ORDER BY RAND() "
					+ "LIMIT 0, ?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, testId);
			stmt.setInt(2, count);
			rs = stmt.executeQuery();
			
			return fromResultSetBase(rs);
		} finally {
			if (rs != null)	rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static List<BaseQuestion> fetchQuestionRandomly(long testId, long topicId,
			int quantity) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ques_id, ques_content, ques_level, ques_topic, "
					+ "ques_author, ques_mark, ques_create_date " 
					+ "FROM tblQuestion "
					+ "WHERE ques_id NOT IN " 
					+ "(SELECT ques_id FROM vwTestQuestion WHERE test_id = ?) "
					+ "AND ques_topic = ? " 
					+ "LIMIT ?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, testId);
			stmt.setLong(2, topicId);
			stmt.setInt(3, quantity);
			rs = stmt.executeQuery();
			return fromResultSetBase(rs);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static void main(String[] args) throws SQLException, IOException {
		System.out.println(Arrays.deepToString(fetchCounts(1, new long[] { -1 },
				new int[] { -1 }, new int[] { -1 })));
		System.out.println(Arrays.deepToString(fetchCounts(1, new long[] { 13, 15 },
				new int[] { 3 }, new int[] { 0, 1 })));
		System.out.println(Arrays.deepToString(fetchCounts(1, new long[] { -1 },
				new int[] { 3 }, new int[] { 0, 1 })));
		System.out.println(Arrays.deepToString(fetchCounts(1, new long[] { 13, 15 },
				new int[] { -1 }, new int[] { 0, 1 })));
		System.out.println(Arrays.deepToString(fetchCounts(1, new long[] { 13, 15 },
				new int[] { -1 }, new int[] { -1 })));
		System.out.println(Arrays.deepToString(fetchCounts(1, new long[] { -1 },
				new int[] { 3 }, new int[] { -1 })));
		System.out.println(Arrays.deepToString(fetchCounts(1, new long[] { -1 },
				new int[] { -1 }, new int[] { 0 })));
		System.in.read();
	}

}
